package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.Condition;
import cn.edu.sdjzu.xg.bysj.domain.Pagination;
import cn.edu.sdjzu.xg.bysj.domain.University;
import cn.edu.sdjzu.xg.bysj.exception.BysjException;
import cn.edu.sdjzu.xg.bysj.util.JdbcHelper;

import java.sql.*;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public class UniversityDao {
    private static UniversityDao universityDao = new UniversityDao();
    private static Collection<University> universities;
    //将唯一构造器设置成私有，阻止在本类外成本类的对象
    private UniversityDao(){
    }
    public static UniversityDao getInstance(){
        return universityDao;
    }
    public Collection<University> findAll(Connection connection, List<Condition> conditionList, Pagination pagination) throws SQLException, BysjException {
        //创建University类型集合类对象，HashSet不保证结果中元素的顺序
        Collection<University> desiredUniversiyies = new HashSet<University>();
        //创建查询university的主语句
        StringBuilder sql_select = new StringBuilder("select * from university");
        //如果有条件，则生成条件子句，并附加到主语句的后面
        if (conditionList != null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        //获取university中的元组总数
        int totalNum = CommonDao.getInstance().count(connection,"cn.edu.sdjzu.xg.bysj.domain.University",conditionList);
        //获得查询university的语句对象
        String paginationClause = pagination.toLimitClause(totalNum);
        sql_select.append(paginationClause);
        PreparedStatement pstmt_select = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet = pstmt_select.executeQuery();
        //遍历resultSet
        while (resultSet.next()){
            //获得university表中当前元组的ID字段的值
            int id = resultSet.getInt("id");
            String description = resultSet.getString("description");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            //以当前元组的各个字段值为参数，创建University对象
            University university = new University(id,description,no,remarks);
            //将当前元组代表的University对象添加到DesiredSchools中
            desiredUniversiyies.add(university);
        }
        JdbcHelper.close(resultSet,pstmt_select,connection);
        return desiredUniversiyies;
    }
    public int add(Connection connection, University university) throws SQLException, BysjException {
        //获取数据库连接
        //准备预编译的语句
        PreparedStatement pr = connection.prepareStatement("insert into university " +
                "(description,no,remarks) values (?,?,?)", Statement.RETURN_GENERATED_KEYS);
        pr.setString(1,university.getDescription());
        pr.setString(2,university.getNo());
        pr.setString(3,university.getRemarks());
        // 执行预编译语句，用其返回值，影响的行数为affectedRowNum赋值
        int rowAffectedNum = pr.executeUpdate();
        int idOfAddedTeacher = 0;
        ResultSet resultSet = pr.getGeneratedKeys(); // 获得生成的主键集合
        if (resultSet.next()){
            idOfAddedTeacher = resultSet.getInt(1); // 获得第一列的值（id）
        }
        resultSet.close();
        pr.close();
        // 本方法是事务的一部分，不能释放资源
        // 新增元组的id
        return idOfAddedTeacher;
    }
    public University find(int id)throws SQLException {
        //创建university类型的引用，暂时不创建对象
        University desiredUniversity = null;
        //获得数据库的连接
        Connection connection = JdbcHelper.getConn();
        String sql_select_id = "select * from university where id=?";
        PreparedStatement pstmt_select_id = connection.prepareStatement(sql_select_id);
        pstmt_select_id.setInt(1,id);
        ResultSet resultSet_select_id = pstmt_select_id.executeQuery();
        //如果表中存在id对应的元组，则获得表中的字段值，并创建对象
        if (resultSet_select_id.next()){
            String description = resultSet_select_id.getString("description");
            String  no = resultSet_select_id.getString("no");
            String remarks = resultSet_select_id.getString("remarks");
            desiredUniversity = new University(id,description,no,remarks);
        }
        //关闭三个资源
        JdbcHelper.close(resultSet_select_id,pstmt_select_id,connection);
        return desiredUniversity;
    }

    public boolean delete(int id, Connection connection) throws SQLException, BysjException {
        PreparedStatement pr = connection.prepareStatement("delete from university where id=?");
        pr.setInt(1,id);
        int affectedRow = pr.executeUpdate();
        pr.close();
        return affectedRow > 0;
    }

    public boolean update(Connection connection, University university) throws SQLException, BysjException {
        //获取数据库连接
        //准备预编译的语句
        String sql_insert = "update university " +
                "set description=?,no=?,remarks=? " + "where id=?";
        PreparedStatement pstmt_insert = connection.prepareStatement(sql_insert);
        pstmt_insert.setString(1,university.getDescription());
        pstmt_insert.setString(2,university.getNo());
        pstmt_insert.setString(3,university.getRemarks());
        pstmt_insert.setInt(4,university.getId());// 设置where=id,根据id找到唯一的元组
        //执行预编译语句，影响的行数保存在rowAffected中
        int rowAffected = pstmt_insert.executeUpdate();
        pstmt_insert.close();
        return rowAffected > 0;
    }
}
